﻿using System;
using System.Collections.Generic;
using System.Data;
using Mono.Data.Sqlite;

namespace CGF
{
    //数据库操作助手（一个数据库new一个新实例）
    public class SQLite
    {
        public const string key_createtime = "create_time";
        public const string key_updatetime = "update_time";

        //数据库目录
        private string dbPath;
        private string dbPassword;

        //创建一个新的数据库管理助手（一个库对应一个实例）
        public SQLite(string filePath)
        {
            this.dbPath = filePath;
            //非Windows平台不支持加密
            this.dbPassword = null;
            try
            {
                SqliteConnection conn = new SqliteConnection("Data Source=" + dbPath);
                conn.Open();
                if (!string.IsNullOrEmpty(dbPassword))
                {
                    conn.ChangePassword(dbPassword);
                }
            }
            catch
            {
                Console.WriteLine("数据库已存在");
            }
        }

        //判断表是否存在
        public bool IsContainsTable(string tableName)
        {
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                string sql = "select count(*) from sqlite_master where type='table' and name='" + tableName + "'";
                using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                {
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    return count > 0;
                }
            }
        }

        //创建表(自动添加id和时间字段并启用默认值)
        public void CreateTableIfNotExist(string tableName, Dictionary<string, Type> keysAndTypes)
        {
            if (keysAndTypes.ContainsKey(key_createtime))
            {
                keysAndTypes.Remove(key_createtime);
            }
            if (keysAndTypes.ContainsKey(key_updatetime))
            {
                keysAndTypes.Remove(key_updatetime);
            }
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                string keysStr = "id integer primary key autoincrement";
                foreach (var item in keysAndTypes)
                {
                    keysStr += "," + item.Key + " " + TypeToString(item.Value);
                    string defaultValue = GetDefaultValue(item.Value);
                    if (!string.IsNullOrEmpty(defaultValue))
                    {
                        keysStr += " default " + GetDefaultValue(item.Value);
                    }
                }
                //这里默认时间不用current_timestamp，否则只能读到柏林时间
                keysStr += "," + key_createtime + " datetime default(datetime('now','localtime'))";
                keysStr += "," + key_updatetime + " datetime default(datetime('now','localtime'))";
                string sql = "create table if not exists " + tableName + "(" + keysStr + ")";
                using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

        //删除表
        public void DeleteTable(string tableName)
        {
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                string sql = "drop table if exists " + tableName;
                using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

        //检查字段是否已创建
        public bool IsContainsKey(string tableName, string key)
        {
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                string sql = "pragma table_info(" + tableName + ")";
                using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var columnName = reader.GetString(1);
                            if (columnName == key)
                            {
                                return true;
                            }
                        }
                    }
                }
            }
            return false;
        }
        
        //添加一个字段
        public void AddKey(string tableName, string key, Type type)
        {
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                string sql = $"alter table {tableName} add column {key} {TypeToString(type)}";
                using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        //查询单条数据
        public DataRow SelectOne(string query)
        {
            if (!query.Contains("limit 1"))
            {
                query += " limit 1";
            }
            DataTable datas = Select(query);
            if (datas != null && datas.Rows.Count > 0)
            {
                return datas.Rows[0];
            }
            else
            {
                return null;
            }
        }

        // 查询多条数据
        // 读取方式：foreach (DataRow item in data.Rows){ Debug.Log((string)item["content"]) };
        public DataTable Select(string query)
        {
            DataTable dataTable = new DataTable();
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                Debug.Log(query);
                using (SqliteCommand command = new SqliteCommand(conn))
                {
                    command.CommandText = query;
                    SqliteDataReader reader = command.ExecuteReader();
                    dataTable = SqliteDataReaderToDataTable(reader);
                    reader.Close();
                    conn.Close();
                }
            }
            return dataTable;
        }
        
        //检查记录是否存在
        public bool IsExist(string tableName, string where)
        {
            return SelectOne($"select id from {tableName} where {where}") != null;
        }

        //查询数据条数
        public int Count(string query)
        {
            int amount = 0;
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                Debug.Log(query);
                using (SqliteCommand command = new SqliteCommand(conn))
                {
                    command.CommandText = query;
                    amount = Convert.ToInt32(command.ExecuteScalar());
                    conn.Close();
                }
            }
            return amount;
        }

        //修改数据
        public void Exec(string query)
        {
            using (SqliteConnection conn = new SqliteConnection("data source=" + dbPath))
            {
                CheckOpen(conn);
                Debug.Log(query);
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        //检查打开状态
        private void CheckOpen(SqliteConnection conn)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    if (!string.IsNullOrEmpty(dbPassword))
                    {
                        conn.SetPassword(dbPassword);
                    }
                    conn.Open();
                }
            }
            catch (Exception ex)
            {
                //Common.Log2Net($"聊天模块使用SQLite3失败：{ex}");
            }
        }

        // 类型转sql语句
        private string TypeToString(Type type)
        {
            if (type == typeof(string))
            {
                return "text";
            }
            else if (type == typeof(int))
            {
                return "int";
            }
            else if (type == typeof(long))
            {
                return "integer";
            }
            else if (type == typeof(float) || type == typeof(double))
            {
                return "real";
            }
            else if (type == typeof(bool))
            {
                Console.WriteLine("出错了：不支持bool类型存储，请使用int代替");
                return "integer";
            }
            return "bolb"; //完全根据它的输入存储，二进制可用
        }

        //根据类型返回字段默认值
        private string GetDefaultValue(Type type)
        {
            if (type == typeof(string))
            {
                return "";
            }
            else if (type == typeof(int) || type == typeof(float) || type == typeof(double) || type == typeof(bool))
            {
                return "0";
            }
            return null;
        }

        private DataTable SqliteDataReaderToDataTable(SqliteDataReader reader)
        {
            DataTable dataTable = new DataTable();
            int colCount = reader.FieldCount;
            for (int i = 0; i < colCount; i++)
            {
                dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            dataTable.BeginLoadData();
            object[] objValues = new object[colCount];
            while (reader.Read())
            {
                reader.GetValues(objValues);
                dataTable.LoadDataRow(objValues, true);
            }
            dataTable.EndLoadData();
            return dataTable;
        }
    }
}
